﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dotnet.DB;
namespace Dotnet.DB.SQLite
{
    public class SQLite : DbBase
    {
        #region 类的构造
        /// <summary>
        /// 默认构造，初始SQLite实例对象
        /// </summary>
        public SQLite()
        {
            //base._dbProviderName = "System.Data.SQLite";
        }
        /// <summary>
        /// 初始化SQLite对象，用dbfile文件名来初始对象
        /// </summary>
        /// <param name="dbfile"></param>
        public SQLite(string dbfile) :
            this(dbfile, "")
        {

        }
        /// <summary>
        /// 用文件名，用户名，密码来初始化SQLite对象
        /// </summary>
        /// <param name="dbfile"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        public SQLite(string dbfile, string pwd)
            : this()
        {
            dbfile = dbfile.Trim();
            string fileName = Path.GetFileName(dbfile);
            if (fileName.Equals(dbfile))
            {
                fileName = $"{Environment.CurrentDirectory}\\Database\\{dbfile}";
            }
            else
            {
                fileName = dbfile;
            }
            if (!File.Exists(fileName))
            {
                throw new Exception($"{fileName}文件不存在");
            }
            string connectionString;
            if (string.IsNullOrEmpty(pwd))
            {
                connectionString = $"Data Source={fileName};Pooling=true;FailIfMissing=false";
            }
            else
            {
                connectionString = $"Data Source={fileName};Pooling=true;FailIfMissing=false;Password={pwd}";
            }
            base.CreateConnection(connectionString);
        }
        #endregion 类的构造
        protected override DbProviderFactory DbFactory => new SQLiteFactory();
        public override string Database => Connection.DataSource;

        public override DataTable GetDbTables()
        {
            DataTable dt = base.GetDbTables();
            var temp = dt.AsEnumerable().Where(t => t.Field<string>("TABLE_TYPE") == "table");
            if (temp.Count() == 0)
            {
                return dt;
            }
            return temp.CopyToDataTable();
        }
        /*public override DataTable GetTablesJoinLocalColumns(DataTable dtTableSchema, DataTable dtLocal, DataTable dtSchemaCols, string dbName, string tabName, out int count, out double usetime)
        {
            DataTable dt = dtLocal.Copy();
            dt.Rows.Clear();
            count = dtSchemaCols.Rows.Count;
            usetime = UseTime;
            foreach (DataColumn dc in dtTableSchema.Columns)
            {
                string colName = dc.ColumnName;
                string colType = dc.DataType.Name;
                //架构中的字段信息
                DataRow rowSchema = dtSchemaCols.AsEnumerable().FirstOrDefault(t => t.Field<string>("table_name") == dtTableSchema.TableName && t.Field<string>("column_name") == colName);
                var rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("col_name") == colName && t.Field<string>("db_name") == dbName && t.Field<string>("table_name") == tabName).ToList();
                if (rows.Count() > 0)
                {

                    rows[0]["sort_code"] = rowSchema == null ? 0 : rowSchema["ORDINAL_POSITION"];
                    // rows[0]["col_name"] = colName;
                    rows[0]["data_type"] = colType;
                    rows[0]["table_desc_type"] = dbName;
                    dt.Rows.Add(rows[0]);
                }
                else
                {
                    rows = dtLocal.AsEnumerable().Where(t => t.Field<string>("col_name") == colName).ToList();
                    if (rows.Count() > 0)
                    {
                        DataRow row = rows[0];
                        row["id"] = "";
                        row["sort_code"] = rowSchema == null ? 0 : rowSchema["ORDINAL_POSITION"];
                        row["col_name"] = colName;
                        row["data_type"] = colType;
                        row["db_name"] = dbName;
                        row["table_name"] = tabName;
                        row["col_desc"] = $"{row["col_desc"].ToString()}[{row["table_name"].ToString()}]";
                        row["table_desc_type"] = $"{DescType.DB}:{ row["db_name"]}";
                        dt.Rows.Add(row.ItemArray);
                    }
                    else  //不存在
                    {
                        DataRow row = dt.NewRow();
                        row["sort_code"] = rowSchema == null ? 0 : rowSchema["ORDINAL_POSITION"];
                        row["col_name"] = colName;
                        row["data_type"] = colType;
                        row["db_name"] = dbName;
                        row["table_name"] = tabName;
                        row["col_desc"] = "ZZZ";
                        dt.Rows.Add(row);
                    }
                }
            }
            return dt;
        }*/
        public override DataTable ExecuteDataTableAsync(string connString, string strSql,out double usetime)
        {
            Stopwatch sw = Stopwatch.StartNew();
            DataTable dt = new DataTable();
            SQLiteConnection connection = new SQLiteConnection(connString);
            try
            {
                connection.Open();
                SQLiteCommand command = new SQLiteCommand(strSql, connection);
                SQLiteDataAdapter oleDbDataAdapter = new SQLiteDataAdapter();
                oleDbDataAdapter.SelectCommand = command;
                oleDbDataAdapter.Fill(dt);
            }
            catch (Exception ex)
            {
                throw new Exception($"【异常】{ex.Message}【连接】{connString}【SQL】{strSql}");
            }
            finally
            {
                connection.Close();
            }
            usetime = Math.Round(sw.Elapsed.TotalMilliseconds, 2);
            return dt;
        }
        public override object ExecuteScalarAsync(string connString, string strSql, out double usetime)
        {
            object ret = null;
            Stopwatch sw = Stopwatch.StartNew();
            SQLiteConnection connection = new SQLiteConnection(connString);
            try
            {
                connection.Open();
                SQLiteCommand command = new SQLiteCommand(strSql, connection);
                ret = command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new Exception($"【异常】{ex.Message}【连接串】{connString}【SQL】{strSql}");
            }
            finally
            {
                connection.Close();
            }
            usetime = Math.Round(sw.Elapsed.TotalMilliseconds, 2);
            return ret;
        }

        public override string GetTop1Sql(string tableName, int top)
        {
            string strSql = $"select *from {tableName} limit {top}";
            return strSql;
        }
    }
}
